/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import model.Country;

/**
 * Document : CountryDAO
 *
 * Created on : 13/03/2013, 13:17:25
 *
 * Author : Fiúza <rbfiuza@gmail.com>
 */
public class CountryDAO {

    Connection conn = null;
    PreparedStatement stmt = null;
    String sql = null;

    public ArrayList<Country> getItems(String start, String limit, String query) {
        ArrayList<Country> countryList = new ArrayList<Country>();

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "Select * from COUNTRY ";
            if (query != null) {
                if (query.trim().equalsIgnoreCase("")) {
                    sql = sql + "where code = '@@@' ";
                } else {
                    query = query.toUpperCase();
                    sql = sql + "where ucase(name) like '" + query + "%' ";
                }
            }

            sql = sql + "order by name,code LIMIT ?,?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, Integer.parseInt(start));
            stmt.setInt(2, Integer.parseInt(limit));
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                Country country = new Country();
                country.setCode(rs.getString("code").trim());
                country.setName(rs.getString("name").trim());
                country.setContinent(rs.getString("continent").trim());
                country.setRegion(rs.getString("region").trim());
                country.setLifeExpectancy(rs.getString("lifeExpectancy") == null ? new Double(0) : Double.parseDouble(rs.getString("lifeExpectancy").trim()));
                country.setGnp(rs.getString("gnp") == null ? new Double(0) : Double.parseDouble(rs.getString("gnp").trim()));
                countryList.add(country);
            }

            rs.close();
            stmt.close();
            stmt = null;


            conn.close();
            conn = null;

        } catch (Exception e) {
            System.out.println(e);
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return countryList;
    }

    public int getTotalCount(String query) {
        int totalCount = 0;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "mysql");

            sql = "Select count(*) from COUNTRY ";
            if (query != null) {
                if (query.trim().equalsIgnoreCase("")) {
                    sql = sql + "where code = '@@@' ";
                } else {
                    query = query.toUpperCase();
                    sql = sql + "where ucase(name) like '" + query + "%' ";
                }
            }
            sql = sql + "order by name,code";
            stmt = conn.prepareStatement(sql);

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                totalCount = rs.getInt(1);
                break;
            }

            rs.close();
            stmt.close();
            stmt = null;


            conn.close();
            conn = null;

        } catch (Exception e) {
            System.out.println(e);
        } finally {


            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here           
                }

                stmt = null;
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException sqlex) {
                    // ignore -- as we can't do anything about it here     
                }

                conn = null;
            }
        }

        return totalCount;

    }
}